﻿using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data.SqlClient;
using System.Data;

namespace DAO
{
    public class NHANVIENDAO
    {
        ///CỦA AI THÌ TỰ ĐÓNG KHUNG VÀO. TRÁNH XÓA NHẦM CODE
        /////////////////CỦA LINH///////////////////
        public int DangNhap(NHANVIENDTO NVDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand( "LINH01_DANGNHAP", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int);
            command.Parameters["@MaNV"].Value = NVDTO.MaNV;
            command.Parameters.Add("@MatKhau", System.Data.SqlDbType.NVarChar);
            command.Parameters["@MatKhau"].Value = NVDTO.MatKhau;
            
            int x = Convert.ToInt32(command.ExecuteScalar());

            conn.Close();
            return x;
        }
        public int LayMaChucVu(NHANVIENDTO NVDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH02_LAYMACHUCVU", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int);
            command.Parameters["@MaNV"].Value = NVDTO.MaNV;

            int x = Convert.ToInt32(command.ExecuteScalar());

            conn.Close();
            return x;
        }
        public string LayTenNV(NHANVIENDTO NVDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH04_LAYTENNHANVIEN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int);
            command.Parameters["@MaNV"].Value = NVDTO.MaNV;

            string x = command.ExecuteScalar().ToString();
            
            conn.Close();
            return x;
        }
        public List<NHANVIENDTO> LayDSNVDatBan()
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH05_LAYDSNVDATBAN", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader reader = command.ExecuteReader();
            List<NHANVIENDTO> ds = new List<NHANVIENDTO>();
            while (reader.Read())
            {
                NHANVIENDTO NVDTO = new NHANVIENDTO();
                NVDTO.MaNV = reader.GetInt32(0);
                ds.Add(NVDTO);
            }

            conn.Close();
            return ds;
        }
        public List<NHANVIENDTO> LayDSNVGoiMon()
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH06_LAYDSNVGOIMON", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataReader reader = command.ExecuteReader();
            List<NHANVIENDTO> ds = new List<NHANVIENDTO>();
            while (reader.Read())
            {
                NHANVIENDTO NVDTO = new NHANVIENDTO();
                NVDTO.MaNV = reader.GetInt32(0);
                ds.Add(NVDTO);
            }

            conn.Close();
            return ds;
        }
        ///////////////////////////////////////
        public DataTable LayDSNV()
        {
            DataTable dt = new DataTable();

            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("AllStaff", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
           
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public DataTable LayDSNVtheoCV(CHUCVUDTO CvDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("LayDSNVtheoCV", conn);
            command.Parameters.Add("@MaCV", System.Data.SqlDbType.Int).Value = CvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public DataTable LayDSNVtheoTenCV(CHUCVUDTO CvDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("LayDSNVtheoTenCV", conn);
            command.Parameters.Add("@TenCV", System.Data.SqlDbType.Text).Value = CvDTO.TenCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);

            conn.Close();
            return dt;
        }
        public int ThemNV(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("ThemNV", conn);
            command.Parameters.Add("@TenNV", System.Data.SqlDbType.NText).Value = NvDTO.TenNV;
            command.Parameters.Add("@MatKhau", System.Data.SqlDbType.Text).Value = NvDTO.MatKhau;
            command.Parameters.Add("@MaCV", System.Data.SqlDbType.Int).Value = NvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteScalar();
            conn.Close();
            return 0;
        }
        public int SuaNVvoiMK(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("SuaThongTinNV", conn);
            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int).Value = NvDTO.MaNV;
            command.Parameters.Add("@TenNV", System.Data.SqlDbType.NText).Value = NvDTO.TenNV;
            command.Parameters.Add("@MatKhau", System.Data.SqlDbType.Text).Value = NvDTO.MatKhau;
            command.Parameters.Add("@MaCV", System.Data.SqlDbType.Int).Value = NvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteScalar();
            conn.Close();
            return 0;
        }
        public int SuaNVkoMK(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("SuaThongTinNV2", conn);
            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int).Value = NvDTO.MaNV;
            command.Parameters.Add("@TenNV", System.Data.SqlDbType.NText).Value = NvDTO.TenNV;
            command.Parameters.Add("@MaCV", System.Data.SqlDbType.Int).Value = NvDTO.MaCV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteScalar();
            conn.Close();
            return 0;
        }
        public int XoaNV(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("XoaNV2", conn);
            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int).Value = NvDTO.MaNV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteScalar();
            conn.Close();
            return 0;
        }

        public int ktNV(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("dbo.demNVtrongHD", conn);
            command.Parameters.Add("@MaNV", SqlDbType.Int).Value = NvDTO.MaNV;
            command.Parameters.Add("@dem", SqlDbType.Int);
            command.Parameters["@dem"].Direction = ParameterDirection.ReturnValue;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
            return Convert.ToInt32(command.Parameters["@dem"].Value);
        }

        public DataTable TimNVtheoTen(NHANVIENDTO NvDTO)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("TimNVtheoTen", conn);
            command.Parameters.Add("@TenNV", System.Data.SqlDbType.NText).Value = NvDTO.TenNV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public int LayMaCVtheoMaNV(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("dbo.LayMaCV", conn);
            command.Parameters.Add("@TenNV", System.Data.SqlDbType.NText).Value = NvDTO.TenNV;
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaNV", SqlDbType.Int).Value = NvDTO.MaNV;
            int kq = command.ExecuteNonQuery();
            conn.Close();
            return kq;
        }

        public int DangNhap2(NHANVIENDTO NVDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();

            SqlCommand command = new SqlCommand("LINH01_DANGNHAP", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;

            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int);
            command.Parameters["@MaNV"].Value = NVDTO.MaNV;
            command.Parameters.Add("@MatKhau", System.Data.SqlDbType.NChar);
            command.Parameters["@MatKhau"].Value = NVDTO.MatKhau;

            int x = Convert.ToInt32(command.ExecuteScalar());

            conn.Close();
            return x;
        }

        public DataTable dsNVxoa()
        {
            DataTable dt = new DataTable();
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("NVxoa", conn);
            command.CommandType = System.Data.CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(command);
            da.Fill(dt);
            conn.Close();
            return dt;
        }

        public void phuchoiNV(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("phuchoiNV", conn);
            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int).Value = NvDTO.MaNV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }
        public void xoaNV_4ever(NHANVIENDTO NvDTO)
        {
            SqlConnection conn = DataProvider.ConnectDB();
            SqlCommand command = new SqlCommand("XoaNV_4ever", conn);
            command.Parameters.Add("@MaNV", System.Data.SqlDbType.Int).Value = NvDTO.MaNV;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.ExecuteNonQuery();
            conn.Close();
        }
    }
}
